<?php
/**
 * Created by PhpStorm.
 * User: chenxiaogang
 * Date: 2017/11/10
 * Time: 上午11:17
 */

namespace User\Model;
use Common\Model\CommonModel;

/**
 * Class Orders
 * @package     User\Model
 * @description 订单表
 * @end
 */
class Orders extends CommonModel
{
    protected $connection = 'DB_CONFIG2';
    protected $trueTableName = 'orders';

    /**
     * @use          [得到用户借款信息 - 借款金额和借款笔数]
     * @author       chenxiaogang
     * @param $user_id
     * @return array
     */
    public function getBorrow($user_id)
    {
        $find = $this->field( [
            'count(id)' => 'leiji_count',
            'if(sum(amount) is null,"0.00",sum(amount))' => 'leiji_amount',
        ] )->where( [ 'user_id' => $user_id ] )->find();
        return $find;
    }

    /**
     * @use          [今日已还款列表]
     * @author       chenxiaogang
     * @param $where
     * @param $limit
     * @return mixed
     */
    public function get_today_paid_list($where, $limit)
    {
        $data = $this->alias( 'o' )
            ->field( 'o.id,o.user_id,o.`order_id`,u.name,u.mobile,u.`identity`,
o.`amount`,SUM(l.`amount`) AS has_paid,o.loan_day,o.`stages`,o.`crtime`,u.account_status,o.status,o.lastuser_name,
o.loan_user,l.types' )
            ->join('`system_user` u ON o.user_id=u.id','left')
            ->join( 'system_log l ON o.id=l.user_id AND (l.types=4 OR l.types=7)','left' )
            ->where( $where )
            ->limit( $limit )
            ->group('o.id')
            ->select();
        return $data;
    }

    public function get_today_paid_total($where)
    {
        $data = $this->alias( 'o' )
            ->field( 'o.`amount`,SUM(l.`amount`) AS has_paid' )
            ->join('`system_user` u ON o.user_id=u.id','left')
            ->join( 'system_log l ON o.id=l.user_id AND (l.types=4 OR l.types=7)','left' )
            ->where( $where )
            ->group('o.id')
            ->select();
        $total['total_amount'] = array_sum(array_column($data,'amount'));
        $total['total_has_paid'] = array_sum(array_column($data,'has_paid'));
        return $total;
    }

    /**
     * @use          [今日已还款列表 - 条数]
     * @author       chenxiaogang
     * @param $where
     * @return int
     */
    public function get_today_paid_list_count($where)
    {
        $data = $this->alias( 'o' )
            ->field('o.id')
            ->join('`system_user` u ON o.user_id=u.id','left')
            ->join( 'system_log l ON o.id=l.user_id AND (l.types=4 OR l.types=7)','left' )
            ->where( $where )
            ->group('o.id')
            ->select();
        $count = count($data);
        return $count;
    }

    /**
     * @use          [计算一批订单的已还款率]
     * @author       chenxiaogang
     * @ids array|string
     */
    public function cal_paid_rate_by_in($ids){
        $fields = [
            'o.id',
            'count(if(s.status=2,s.id,null))/o.stages'=>'has_paid_rate',
        ];
        $data = $this->alias('o')
            ->field($fields)
            ->join('orders_sub s on o.id=s.pid','left')
            ->where(['o.id'=>['in',$ids]])
            ->group('o.id')
            ->select();
        $result = [];
        foreach($data as $v){
            $result[$v['id']]=$v['has_paid_rate'];
        }
        return $result;
    }

    /**
     * @use          [把制定范围订单ID的已交的总服务费查出来]
     * @author       chenxiaogang
     * @param $in
     * @return float
     */
    public function cal_paid_services_by_in($in){
        $fields = 'id,interest+examine+consulting as all_services';
        //计算出各个订单的已还款率
        $rates = $this->cal_paid_rate_by_in($in);
        $data = $this->field($fields)->where(['id'=>['in',$in]])->select();
        foreach($data as $k => $v){
            $rate = (float)$rates[(int)$v['id']];
            $data[$k]['all_services'] = $v['all_services']*$rate;
        }
        $sum = ceil(array_sum(array_column($data,'all_services')));
        return $sum;
    }

    /**
     * @use          [垫审列表]
     * @author       chenxiaogang
     * @param $where
     * @param $limit
     * @return array
     */
    public function padtral_list($where,$limit)
    {
        $fields = 'a.fast_auditing,a.user_id,a.is_display,a.id,a.order_id,a.status,a.amount,a.crtime,a.stages,a.lastuser_name,a.loan_time,a.loan_day,a.loan_user,';
        $fields .= 'b.name,b.mobile,b.account_status,b.byusers,b.identity,b.photo_type,b.education,b.code';
        $data = $this->alias('a')
            ->field( $fields )
            ->join( "`system_user` b ON a.user_id=b.id",'LEFT' )
            ->where( $where )
            ->order( "b.modtime DESC,a.loan_time DESC,a.crtime DESC,a.id DESC" )
            ->limit( $limit )
            ->select();
        return $data;
    }

    /**
     * @use          [电审列表合计]
     * @author       chenxiaogang
     * @param $where
     * @return mixed
     */
    public function padtral_list_total($where)
    {
        $data = $this->alias('a')
            ->field( 'sum(a.amount) as total_amount' )
            ->join( "`system_user` b ON a.user_id=b.id",'LEFT' )
            ->where( $where )
            ->find();
        return $data;
    }

    /**
     * @use          [垫审列表条数]
     * @author       chenxiaogang
     * @param $where
     * @return int
     */
    public function padtral_list_count($where)
    {
        $count = $this->alias('a')
            ->join( "system_user b ON a.user_id=b.id",'LEFT' )
            ->where( $where )
            ->count('a.id');
        return $count;
    }
    

}